# -*- coding:utf8 -*-
"""
Author: gallopingvijay
Email: 1937832819@qq.com
Website: https://www.choudalao.com
"""

import xlrd
import pymysql
import time
from itertools import groupby
import os
import yaml


class Excel(object):
    def __init__(self):
        # 配置
        self.configs = self.get_config()
        self.conn = pymysql.connect(
            host=self.configs['mysql_config']['host'],
            port=self.configs['mysql_config']['port'],
            user=self.configs['mysql_config']['user'],
            password=self.configs['mysql_config']['password'],
            db=self.configs['mysql_config']['db'],
            charset=self.configs['mysql_config']['charset']
        )
        # 创建一个游标
        self.cursor = self.conn.cursor()
        # 存储数据的字典
        self.dict = {}

    def get_config(self):
        '''
        获取config.yaml配置
        :return:
        '''
        # 获取当前文件的Realpath
        fileNamePath = os.path.split(os.path.realpath(__file__))[0]
        # 读取文件
        yamlPath = os.path.join(fileNamePath, 'config.yaml')
        # 加上 ,encoding='utf-8'，处理配置文件中含中文出现乱码的情况。
        file = open(yamlPath, 'r', encoding='utf-8')
        # 读取文件
        cont = file.read()
        # 返回配置
        return yaml.safe_load(cont)

    def pymysql_action(self, sql):
        # 创建一个游标
        # 执行 SQL 语句
        self.cursor.execute(sql)
        # 提交
        self.conn.commit()

    def del_null(self, list):
        '''
        清理空数据
        :param list:
        :return:
        '''
        list = [i for i in list if i != '']
        return list

    def filter_field(self, field, typeof='string'):
        '''
        处理每一个写入数据库的值
        :param field: 字段值
        :param typeof: 类型：string,decimal(四舍五入保留两位),num
        :return:
        '''
        if field == '--' or field == '':
            field = '0'
        elif typeof == 'decimal':
            field = round(float(field), 2)
        elif typeof == 'num':
            field = int(field)
        else:
            field = str(field)
            field = field.lstrip().rstrip()
        return field

    def deal_excel(self, path):
        workbook = xlrd.open_workbook(path)
        # 获取所有sheet
        sheet_name = workbook.sheet_names()[0]
        # 根据sheet索引或者名称获取sheet内容
        sheet = workbook.sheet_by_index(0)  # sheet索引从0开始
        # sheet的名称，行数，列数
        nrows = sheet.nrows
        # ncols = sheet.ncols
        # 获取整行和整列的值（数组）
        # rows = sheet.row_values(2)  # 获取第2行内容
        # cols = sheet.col_values(3) # 获取第3列内容

        if sheet_name != '客户交易结算日报':
            print('...不是需要的数据')
            exit()

        ding_shi = False  # 如果是逐日盯市，才写入数据库
        cny_i = 0  # cny 期货期权账户出入金明细（单位：人民币）开始
        usd_i = 0  # 期货期权账户出入金明细（单位：美元）
        deal_row_i = 0  # 期货成交汇总 开始的行号
        position_row_i = 0  # 期货持仓汇总 开始的行号
        for i in range(nrows):
            try:
                rows = sheet.row_values(i)  # 获取第i行内容
                deal_rows = self.del_null(rows)
                if len(deal_rows) < 1:
                    # print(f'第{i + 1}行没有数据')
                    continue
                if '客户交易结算日报(逐日盯市)' in deal_rows:
                    ding_shi = True
                if i > 4 and ding_shi is False:  # 表头不是逐日盯市，不处理
                    print('...不是逐日盯市表格，不处理')
                    break
                # 客户期货期权内部资金账户
                if '客户期货期权内部资金账户' in deal_rows:
                    self.dict['fund_account'] = deal_rows[1]
                    self.dict['deal_time'] = deal_rows[3]
                    self.dict['created_at'] = time.strftime(
                        '%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
                    timeArray = time.strptime(
                        self.dict['deal_time'], "%Y-%m-%d")
                    self.dict['deal_time_stamp'] = int(time.mktime(timeArray))
                    self.dict['out_money_cny'] = self.dict['enter_money_cny'] = self.dict['out_money_usd'] = self.dict[
                        'enter_money_usd'] = 0
                    self.dict['exchange_name'] = ''
                    self.dict['transaction_fees'] = '0.00'
                    self.dict['reporting_fee'] = '0.00'
                    self.dict['position_buy_position'] = '0.00'
                    self.dict['position_sell_position'] = '0.00'
                    self.dict['position_profit_loss'] = '0.00'
                    self.dict['position_trading_margin'] = '0.00'
                    self.dict['deal_num'] = 0
                    self.dict['deal_turnover'] = '0.00'
                    self.dict['deal_fee'] = '0.00'
                    self.dict['deal_profit_loss'] = '0.00'
                if '客户名称' in deal_rows:
                    self.dict['admin_name'] = deal_rows[1].strip()
                    self.dict['query_time'] = deal_rows[3]
                    # 获取用户的后台id
                    admin_sql = "SELECT a.id as admin_id FROM wjf_transaction_users u LEFT JOIN wjf_admins a ON u.admin_id = a.id WHERE u.admin_name = '{0}' LIMIT 1".format(
                        self.dict['admin_name'])
                    self.cursor.execute(admin_sql)
                    first_data = self.cursor.fetchone()
                    if first_data is None:
                        print('没有客户名称为' + str(self.dict['admin_name']) + '的数据')
                        break
                    self.dict['admin_id'] = first_data[0]
                    # 如果数据库存在该用户的该天数据，就跳过
                    summary_sql = "SELECT id FROM `wjf_summary_datas` WHERE `admin_id` = '{0}' and `deal_time_stamp` = '{1}'LIMIT 1".format(
                        self.dict['admin_id'], self.dict['deal_time_stamp'])
                    self.cursor.execute(summary_sql)
                    summary_data = self.cursor.fetchone()
                    if summary_data is not None:
                        print(
                            '客户:' + str(self.dict['admin_name']) + str(self.dict['deal_time']) + '的数据已存在')
                        break
                if '期货公司名称' in deal_rows:
                    self.dict['futures_company'] = deal_rows[1]
                if '上日结存' in deal_rows:
                    self.dict['previous_day_balance'] = deal_rows[1]
                    self.dict['customer_rights'] = deal_rows[3]
                if '当日存取合计' in deal_rows:
                    self.dict['total_access_for_the_day'] = deal_rows[1]
                    self.dict['actual_monetary_funds'] = deal_rows[3]
                if '当日盈亏' in deal_rows:
                    self.dict['profit_loss_day'] = deal_rows[1]
                    self.dict['not_currency_credit_amount'] = deal_rows[3]
                if '当日总权利金' in deal_rows:
                    self.dict['total_royalties_of_the_day'] = deal_rows[1]
                    self.dict['currency_credit_amount'] = deal_rows[3]
                if '当日手续费' in deal_rows:
                    self.dict['day_handling_fee'] = deal_rows[1]
                    self.dict['frozen_funds'] = deal_rows[3]
                if '当日结存' in deal_rows:
                    self.dict['day_balance'] = deal_rows[1]
                    self.dict['margin_occupation'] = deal_rows[3]
                if '可用资金' in deal_rows:
                    self.dict['available_funds'] = deal_rows[1]
                if '风险度' in deal_rows:
                    self.dict['risk'] = deal_rows[1]
                if '追加保证金' in deal_rows:
                    self.dict['margin_call'] = deal_rows[1]
                # 期货期权账户出入金明细（单位：人民币 开始
                if '期货期权账户出入金明细（单位：人民币）' in deal_rows:
                    cny_i = i
                if '合计' in deal_rows and (cny_i > 0):
                    self.dict['out_money_cny'] = self.filter_field(
                        deal_rows[1], 'decimal')
                    self.dict['enter_money_cny'] = self.filter_field(
                        deal_rows[2], 'decimal')
                    cny_i = 0
                # 期货期权账户出入金明细（单位：美元）
                if '期货期权账户出入金明细（单位：美元）' in deal_rows:
                    usd_i = i
                if '合计' in deal_rows and (usd_i > 0):
                    usd_i = 0
                    self.dict['out_money_usd'] = self.filter_field(
                        deal_rows[1], 'decimal')
                    self.dict['enter_money_usd'] = self.filter_field(
                        deal_rows[2], 'decimal')
                # 期货成交汇总 开始
                if '期货成交汇总' in deal_rows:  # 期货成交汇总 开始
                    deal_row_i = i
                if '合约' in deal_rows:
                    continue
                if ('合计' in deal_rows) and (deal_row_i > 0) and (position_row_i <= 0):  # 期货成交汇总 结束
                    deal_row_i = 0
                    self.dict['deal_num'] = self.filter_field(
                        deal_rows[1], 'num')
                    self.dict['deal_turnover'] = self.filter_field(
                        deal_rows[2], 'decimal')
                    self.dict['deal_fee'] = self.filter_field(
                        deal_rows[3], 'decimal')
                    self.dict['deal_profit_loss'] = self.filter_field(
                        deal_rows[4], 'decimal')
                if i > deal_row_i and deal_row_i > 0:  # 成交汇总
                    deal_rows.append(self.dict['admin_name'])
                    deal_rows.append(self.dict['query_time'])
                    deal_rows.append(self.dict['deal_time'])
                    deal_rows.append(self.dict['created_at'])
                    # 合约中提取交易所code
                    contract = deal_rows[0]
                    contract_list = [''.join(list(g)) for k, g in groupby(
                        contract, key=lambda x: x.isdigit())]
                    exchange_code = contract_list[0]
                    exchange_num = contract_list[1]
                    deal_rows.append(exchange_code)
                    deal_rows.append(exchange_num)
                    # 字符串转数字，以免数据库保存报错
                    deal_rows[0] = self.filter_field(deal_rows[0])
                    deal_rows[1] = self.filter_field(deal_rows[1])
                    deal_rows[2] = self.filter_field(deal_rows[2])
                    deal_rows[3] = self.filter_field(deal_rows[3], 'decimal')
                    deal_rows[4] = self.filter_field(deal_rows[4], 'num')
                    deal_rows[5] = self.filter_field(deal_rows[5], 'decimal')
                    deal_rows[8] = self.filter_field(deal_rows[8], 'decimal')
                    deal_rows[6] = self.filter_field(deal_rows[6])
                    deal_rows[7] = self.filter_field(deal_rows[7])
                    deal_rows[9] = self.filter_field(deal_rows[9])
                    deal_rows[10] = self.filter_field(deal_rows[10])
                    deal_rows[11] = self.filter_field(deal_rows[11])
                    deal_rows[12] = self.filter_field(deal_rows[12])
                    deal_rows[13] = self.filter_field(deal_rows[13])
                    deal_rows[14] = self.filter_field(deal_rows[14])

                    sql = "INSERT INTO wjf_deal_datas(contract,buy_sell,speculation,final_price,num,turnover,open_flat,fee,profit_loss,admin_name,query_time,deal_time,created_at,exchange_code,exchange_num,admin_id,deal_time_stamp)VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}');".format(
                        deal_rows[0], deal_rows[1], deal_rows[2], deal_rows[3], deal_rows[4], deal_rows[5],
                        deal_rows[6], deal_rows[7], deal_rows[8], deal_rows[9], deal_rows[10], deal_rows[11],
                        deal_rows[12], deal_rows[13], deal_rows[14], self.dict['admin_id'],
                        self.dict['deal_time_stamp'])

                    self.pymysql_action(sql)
                    # print("..." + str(i + 1) + "行期货成交写入完成")
                if '期货持仓汇总' in deal_rows:  # 期货持仓汇总 开始
                    position_row_i = i
                    deal_row_i = 0
                if ('合计' in deal_rows) and (deal_row_i == 0) and (position_row_i > 0):  # 期货持仓汇总 结束
                    position_row_i = 0
                    self.dict['position_buy_position'] = self.filter_field(
                        deal_rows[1], 'decimal')
                    self.dict['position_sell_position'] = self.filter_field(
                        deal_rows[2], 'decimal')
                    self.dict['position_profit_loss'] = self.filter_field(
                        deal_rows[3], 'decimal')
                    self.dict['position_trading_margin'] = self.filter_field(
                        deal_rows[4], 'decimal')
                    # 写入汇总数据
                    summary_sql = "INSERT INTO wjf_summary_datas(admin_id,admin_name,query_time,deal_time,deal_time_stamp,fund_account,futures_company,previous_day_balance,customer_rights,total_access_for_the_day,actual_monetary_funds,profit_loss_day,not_currency_credit_amount,total_royalties_of_the_day,currency_credit_amount,day_handling_fee,frozen_funds,day_balance,margin_occupation,available_funds,risk,margin_call,out_money_cny,enter_money_cny,out_money_usd,enter_money_usd,exchange_name,transaction_fees,reporting_fee,deal_num,deal_turnover,deal_fee,deal_profit_loss,position_buy_position,position_sell_position,position_profit_loss,position_trading_margin,created_at)VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}',{12},'{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}','{29}','{30}','{31}','{32}','{33}','{34}','{35}','{36}','{37}');".format(
                        self.dict['admin_id'], self.dict['admin_name'], self.dict['query_time'], self.dict['deal_time'],
                        self.dict['deal_time_stamp'], self.dict['fund_account'], self.dict['futures_company'],
                        self.dict['previous_day_balance'], self.dict['customer_rights'],
                        self.dict['total_access_for_the_day'],
                        self.dict['actual_monetary_funds'], self.dict['profit_loss_day'],
                        self.dict['not_currency_credit_amount'], self.dict['total_royalties_of_the_day'],
                        self.dict['currency_credit_amount'], self.dict['day_handling_fee'], self.dict['frozen_funds'],
                        self.dict['day_balance'], self.dict['margin_occupation'], self.dict['available_funds'],
                        self.dict['risk'], self.dict['margin_call'], self.dict['out_money_cny'],
                        self.dict['enter_money_cny'], self.dict['out_money_usd'], self.dict['enter_money_usd'],
                        self.dict['exchange_name'], self.dict['transaction_fees'], self.dict['reporting_fee'],
                        self.dict['deal_num'], self.dict['deal_turnover'], self.dict['deal_fee'],
                        self.dict['deal_profit_loss'], self.dict['position_buy_position'],
                        self.dict['position_sell_position'], self.dict['position_profit_loss'],
                        self.dict['position_trading_margin'], self.dict['created_at'])
                    self.pymysql_action(summary_sql)
                    print("..." + "汇总合计写入完成")
                    break
                if (i > position_row_i) and (position_row_i > 0):  # 期货持仓汇总
                    rows.append(self.dict['admin_name'])
                    rows.append(self.dict['query_time'])
                    rows.append(self.dict['deal_time'])
                    rows.append(self.dict['created_at'])
                    # 合约中提取交易所code
                    contract = rows[0]
                    contract_list = [''.join(list(g)) for k, g in groupby(
                        contract, key=lambda x: x.isdigit())]
                    exchange_code = contract_list[0]
                    exchange_num = contract_list[1]
                    rows.append(exchange_code)
                    rows.append(exchange_num)
                    # 字符串转数字，以免数据库保存报错
                    rows[1] = self.filter_field(rows[1], 'int')
                    rows[2] = self.filter_field(rows[2], 'decimal')
                    rows[3] = self.filter_field(rows[3], 'int')
                    rows[4] = self.filter_field(rows[4], 'decimal')
                    rows[5] = self.filter_field(rows[5], 'decimal')
                    rows[6] = self.filter_field(rows[6], 'decimal')
                    rows[7] = self.filter_field(rows[7], 'decimal')
                    rows[8] = self.filter_field(rows[8], 'decimal')
                    rows[0] = self.filter_field(rows[0])
                    rows[9] = self.filter_field(rows[9])
                    rows[10] = self.filter_field(rows[10])
                    rows[11] = self.filter_field(rows[11])
                    rows[12] = self.filter_field(rows[12])
                    rows[13] = self.filter_field(rows[13])
                    rows[14] = self.filter_field(rows[14])
                    rows[15] = self.filter_field(rows[15])
                    sql = "INSERT INTO wjf_position_datas(contract,buy_position,buy_average_price,sell_position,sell_average_price,settlement_price_yesterday,settlement_price_today,profit_loss,trading_margin,speculation,admin_name,query_time,deal_time,created_at,exchange_code,exchange_num,admin_id,deal_time_stamp)VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}');".format(
                        rows[0], rows[1], rows[2], rows[3], rows[4], rows[5], rows[6], rows[7], rows[8], rows[9],
                        rows[10], rows[11], rows[12], rows[13], rows[14], rows[15], self.dict['admin_id'],
                        self.dict['deal_time_stamp'])
                    self.pymysql_action(sql)
                    # print("..." + str(i + 1) + "行期货持仓写入完成")
                # 当没有期货持仓汇总数据的时候，最后额数据在这里写入，如果有期货持仓汇总，不会执行到这里
                if nrows-1 == i:
                    print('...没有期货持仓汇总，在最后一行写入汇总数据')
                    # 写入汇总数据
                    summary_sql = "INSERT INTO wjf_summary_datas(admin_id,admin_name,query_time,deal_time,deal_time_stamp,fund_account,futures_company,previous_day_balance,customer_rights,total_access_for_the_day,actual_monetary_funds,profit_loss_day,not_currency_credit_amount,total_royalties_of_the_day,currency_credit_amount,day_handling_fee,frozen_funds,day_balance,margin_occupation,available_funds,risk,margin_call,out_money_cny,enter_money_cny,out_money_usd,enter_money_usd,exchange_name,transaction_fees,reporting_fee,deal_num,deal_turnover,deal_fee,deal_profit_loss,position_buy_position,position_sell_position,position_profit_loss,position_trading_margin,created_at)VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}',{12},'{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}','{29}','{30}','{31}','{32}','{33}','{34}','{35}','{36}','{37}');".format(
                        self.dict['admin_id'], self.dict['admin_name'], self.dict['query_time'], self.dict['deal_time'],
                        self.dict['deal_time_stamp'], self.dict['fund_account'], self.dict['futures_company'],
                        self.dict['previous_day_balance'], self.dict['customer_rights'],
                        self.dict['total_access_for_the_day'],
                        self.dict['actual_monetary_funds'], self.dict['profit_loss_day'],
                        self.dict['not_currency_credit_amount'], self.dict['total_royalties_of_the_day'],
                        self.dict['currency_credit_amount'], self.dict['day_handling_fee'], self.dict['frozen_funds'],
                        self.dict['day_balance'], self.dict['margin_occupation'], self.dict['available_funds'],
                        self.dict['risk'], self.dict['margin_call'], self.dict['out_money_cny'],
                        self.dict['enter_money_cny'], self.dict['out_money_usd'], self.dict['enter_money_usd'],
                        self.dict['exchange_name'], self.dict['transaction_fees'], self.dict['reporting_fee'],
                        self.dict['deal_num'], self.dict['deal_turnover'], self.dict['deal_fee'],
                        self.dict['deal_profit_loss'], self.dict['position_buy_position'],
                        self.dict['position_sell_position'], self.dict['position_profit_loss'],
                        self.dict['position_trading_margin'], self.dict['created_at'])
                    self.pymysql_action(summary_sql)
                    print("..." + "汇总合计写入完成")
            except Exception as e:
                print('......' + str(i + 1) + "行有异常抛出:")
                print(e)

    def get_files(self, path):
        '''
        获取所有excel文件
        :param path:路径
        :return:
        '''
        files = os.listdir(path)
        if len(files) < 1:
            print('没有文件')
        else:
            for file in files:
                file_path = path + file
                # 判断后缀
                if '.xls' in file:
                    print('开始读取文件：' + file_path)
                    self.deal_excel(file_path)
                # 删除文件
                try:
                    print('暂不删除文件：' + file_path)
                    # print('删除文件：' + file_path)
                    # os.remove(file_path)
                except Exception as e:
                    print(e)

    def test(self):
        for i in range(5):
            try:
                if i == 3:
                    continue
                print(i)
            except Exception as e:
                print(e)

    def __del__(self):
        # 关闭游标
        self.cursor.close()
        # 关闭数据库连接
        self.conn.close()


if __name__ == '__main__':
    path = os.path.abspath(os.path.dirname(__file__))+'./exel/'
    obj = Excel()
    obj.get_files(path)
